<?php
// public/reports.php
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();

$title = "Reports | JO & COS RMS";

// Determine report type
$reportType = $_GET['type'] ?? 'employees';

// Fetch data depending on report type
if ($reportType === 'documents') {
    $stmt = $pdo->query("
        SELECT d.id,
               e.full_name,
               d.doc_type,
               d.file_path,
               d.remarks,
               d.uploaded_at,
               d.created_at
        FROM documents d
        JOIN employees e ON d.employee_id = e.id
        ORDER BY d.created_at DESC
    ");
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    $stmt = $pdo->query("SELECT id, name, email, role, status, created_at 
                         FROM users 
                         ORDER BY created_at DESC");
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Handle export (unchanged)
if (isset($_GET['export'])) {
    $type = $_GET['export'];
    $filename = $reportType . "_report";

    if ($type === 'csv') {
        header('Content-Type: text/csv');
        header("Content-Disposition: attachment; filename=\"{$filename}.csv\"");
        $out = fopen('php://output', 'w');

        if ($reportType === 'documents') {
            fputcsv($out, array_keys($records[0] ?? []));
            foreach ($records as $r) {
                fputcsv($out, $r);
            }
        } else {
            fputcsv($out, array_keys($records[0] ?? []));
            foreach ($records as $r) {
                fputcsv($out, $r);
            }
        }
        fclose($out);
        exit;
    }

    if ($type === 'excel') {
        require_once __DIR__ . '/../vendor/autoload.php';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        if (!empty($records)) {
            $sheet->fromArray(array_keys($records[0]), NULL, 'A1');
            $row = 2;
            foreach ($records as $r) {
                $sheet->fromArray(array_values($r), NULL, "A{$row}");
                $row++;
            }
        }
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"{$filename}.xlsx\"");
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    if ($type === 'pdf') {
        require_once __DIR__ . '/../vendor/autoload.php';
        $pdf = new \TCPDF();
        $pdf->AddPage();
        $html = "<h2>".ucfirst($reportType)." Report</h2><table border='1' cellpadding='4'><tr>";
        if (!empty($records)) {
            foreach (array_keys($records[0]) as $h) {
                $html .= "<th>".htmlspecialchars($h)."</th>";
            }
            $html .= "</tr>";
            foreach ($records as $r) {
                $html .= "<tr>";
                foreach ($r as $val) {
                    $html .= "<td>".htmlspecialchars((string)$val)."</td>";
                }
                $html .= "</tr>";
            }
        }
        $html .= "</table>";
        $pdf->writeHTML($html);
        $pdf->Output("{$filename}.pdf", 'D');
        exit;
    }
}

include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>

<section class="content-header">
  <div class="container-fluid d-flex justify-content-between align-items-center">
    <h1>Reports</h1>
    <div>
      <a href="?type=employees" class="btn btn-sm btn-outline-primary <?= $reportType==='employees'?'active':'' ?>">Employees</a>
      <a href="?type=documents" class="btn btn-sm btn-outline-secondary <?= $reportType==='documents'?'active':'' ?>">Documents</a>
    </div>
  </div>
</section>

<section class="content">
  <div class="card">
    <div class="card-body">
      <div class="mb-3">
        <a href="?type=<?= $reportType ?>&export=csv" class="btn btn-sm btn-outline-secondary"><i class="fas fa-file-csv"></i> CSV</a>
        <a href="?type=<?= $reportType ?>&export=excel" class="btn btn-sm btn-outline-success"><i class="fas fa-file-excel"></i> Excel</a>
        <a href="?type=<?= $reportType ?>&export=pdf" class="btn btn-sm btn-outline-danger"><i class="fas fa-file-pdf"></i> PDF</a>
      </div>

      <!-- Responsive wrapper -->
      <div class="table-responsive">
        <table id="reportsTable" class="table table-bordered table-striped nowrap" style="width:100%">
          <thead>
            <tr>
              <?php foreach (array_keys($records[0] ?? []) as $col): ?>
                <th><?= ucfirst(str_replace('_',' ',$col)) ?></th>
              <?php endforeach; ?>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($records as $r): ?>
            <tr>
              <?php foreach ($r as $val): ?>
                <td><?= htmlspecialchars((string)$val) ?></td>
              <?php endforeach; ?>
            </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <!-- End Responsive wrapper -->

    </div>
  </div>
</section>

<?php include __DIR__ . '/../includes/footer.php'; ?>

<!-- DataTables -->
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap4.min.css"/>
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap4.min.css"/>

<script>
$(function () {
  $('#reportsTable').DataTable({
    responsive: true,
    autoWidth: false
  });
});
</script>
